
import os
import pandas as pd
import time
import os
from  openpyxl import  Workbook
from sqlalchemy import create_engine
import datetime
from data_template import data_template, sheet_father





class work_sheet(sheet_father):
    def __init__(self, sid, browserName, seller_id, browserOauth, country):
        
        sheet_father.__init__(self, sid, browserName, seller_id, browserOauth, country)
        
    # 广告活动新建
    def create_ad(self, portfolio_id, campaign_name, ad_group_name, targeting_type, budget, bidding_strategy, placements: list, percentages: list, sku, bid_lst, Keyword_product_text_lst, match_type, start_data):
        state = "enabled"
        # 生成广告活动
        lst_campaign = self.create_campaign_lst("create", campaign_name, portfolio_id, campaign_name, targeting_type, budget, bidding_strategy, state, start_data)
        self.add_data(lst_campaign)
        # 生成竞价调整
        for placement, percentage in zip(placements, percentages):
            if percentage:
                
                lst_bid_adjust = self.create_bid_adjust_lst("create", campaign_name, placement, percentage)
                self.add_data(lst_bid_adjust)
        # 生成广告组
        if targeting_type == "manual":
            bid = bid_lst[0]
        elif targeting_type == "auto":
            bid = 0.75
        lst_ad_group = self.create_ad_group_lst("create", campaign_name, ad_group_name, ad_group_name, state, bid)
        self.add_data(lst_ad_group)
        # 生成 广告商品
        lst_product_ad = self.create_product_ad_lst("create", campaign_name, ad_group_name, state, sku)
        self.add_data(lst_product_ad)
        
        if targeting_type == "manual":
            # 生成 关键词投放
            lst_product_ad = self.create_kw_lst("create", campaign_name, ad_group_name, state, bid, Keyword_product_text_lst[0], match_type)
            self.add_data(lst_product_ad)
        elif targeting_type == "auto":
            for bid_name, bid in zip(Keyword_product_text_lst, bid_lst):
                if bid:
                    state = "enabled"
                else:
                    state = "paused"
            # 生成产品投放  (self, operation, campaign_id, ad_group_id,state, bid, product_targeting_expression)
                lst_product_ad = self.create_prod_target_lst("create", campaign_name, ad_group_name, state, bid, bid_name)
                self.add_data(lst_product_ad)
    
    
    def new_ad_create_manual(self, file_path):
        ad_data = pd.read_excel(file_path)
        ad_data = ad_data.fillna("")
        for i, row_data in ad_data.iterrows():
            if row_data['广告活动名称(必填项)'] == "":
                continue
            portfolio_id = row_data['广告组合ID']
            campaign_name = row_data['广告活动名称(必填项)']
            ad_group_name = row_data['广告组名称(必填项)']
            if pd.isna(row_data['广告开始日期']) or row_data['广告开始日期'] == "":
                start_data = (datetime.datetime.now() - datetime.timedelta(days=1)).strftime("%Y%m%d")
            else:
                start_data = row_data['广告开始日期'].strftime("%Y%m%d")
                
            targeting_type = "manual" 
            budget = row_data['广告预算']
            bidding_strategy = row_data['竞价策略']
            placements = ["placement_top", "placement_productpage", "placement_rest_search", "placement_business"]
            percentages = [row_data['搜索结果顶部加倍(%)'], row_data['商品页面加倍(%)'], row_data['搜索结果其余位置加倍(%)'], row_data['企业购加倍(%)']]
            sku = row_data['投放sku(必填项)']
            bid = row_data['竞价']
            Keyword_text = row_data['投放词(必填项)']
            match_type = row_data['匹配类型']
            self.create_ad(portfolio_id, campaign_name, ad_group_name, targeting_type, budget, bidding_strategy, placements, percentages, sku, bid, Keyword_text, match_type, start_data)
        self.save_file()
    
    def new_ad_create_auto(self, file_path):
        targeting_type = "auto"
        match_type = ""
        ad_data = pd.read_excel(file_path)
        ad_data = ad_data.fillna("")
        for i, row_data in ad_data.iterrows():
            if row_data['广告活动名称(必填项)'] == "":
                continue
            sku = row_data['投放sku(必填项)']
            bid_lst = [row_data['紧密匹配-竞价'], row_data['宽泛匹配-竞价'], row_data['同类商品-竞价'], row_data['关联商品-竞价']]
            bid_name = ['close-match', 'loose-match', 'substitutes', 'complements']
            portfolio_id = row_data['广告组合ID']
            campaign_name = row_data['广告活动名称(必填项)']
            ad_group_name = row_data['广告组名称(必填项)']
            if pd.isna(row_data['广告开始日期']) or row_data['广告开始日期'] == "":
                start_data = (datetime.datetime.now() - datetime.timedelta(days=1)).strftime("%Y%m%d")
            else:
                start_data = row_data['广告开始日期'].strftime("%Y%m%d")
            budget = row_data['广告预算']
            bidding_strategy = row_data['竞价策略']
            placements = ["placement_top", "placement_productpage", "placement_rest_search", "placement_business"]
            percentages = [row_data['搜索结果顶部加倍(%)'], row_data['商品页面加倍(%)'], row_data['搜索结果其余位置加倍(%)'], row_data['企业购加倍(%)']]
            self.create_ad(portfolio_id, campaign_name, ad_group_name, targeting_type, budget, bidding_strategy, placements, percentages, sku, bid_lst, bid_name, match_type, start_data)
        self.save_file()
    


ws = work_sheet(123, "1234", "23423", "123123", "1234213")

# ws.new_ad_create_manual("C:/Amazon/xie_jin_li_us/Bulk/ManualCampaignDataTemplate(xie_jin_li_us).xlsx")

ws.new_ad_create_auto("C:/Amazon/xie_jin_li_us/Bulk/AutoCampaignDataTemplate(xie_jin_li_us).xlsx")







